﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MCPlatform.Shared.DAL
{
    public class DataAccess
    {
        //日志

        protected NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();
       // string dbConfig = ConfigurationManager.ConnectionStrings["db_config"].ConnectionString;

      
        string dbConfig = "Data Source = DESKTOP-54DEURV\\SQLEXPRESS03;Initial Catalog = NLog; Integrated Security = True";
       
        SqlConnection? conn;
        SqlCommand? comm;
        SqlDataAdapter? adapter;
        SqlTransaction? trans;

        Dictionary<string, List<string>> TableColmns = new Dictionary<string, List<string>>();
      
        private void Dispose()
        {
            if (adapter != null)
            {
                adapter.Dispose(); adapter = null;
            }
            if (comm != null)
            {
                comm.Dispose(); comm = null;
            }
            if (trans != null)
            {
                trans.Dispose(); trans = null;
            }
            if (conn != null)
            {
                conn.Close(); conn = null;
            }
        }

        private string SetDatas(string sql)
        {
            try
            {
                using (conn = new SqlConnection(dbConfig))
                {
                    // conn.ConnectionTimeout = 30;
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.CommandTimeout = 30;
                    int rows = cmd.ExecuteNonQuery();
                    conn.Close();
                    return "success" + rows;
                }


            }
            catch (Exception)
            {
                return "success" + 0;
                // throw ex;
            }
            finally
            {
                // conn?.Close();
                this.Dispose();
            }
        }

        public string SetData(string deviceName, Dictionary<string, string> values)
        {

            StringBuilder sb_key = new StringBuilder();
            StringBuilder sb_value = new StringBuilder();

            //查找values字典的键和TableColmns[]中值相对应的子项
            foreach (string key in values.Keys)
            {
                if (TableColmns[deviceName].Contains(key))
                {

                    //判断key是否是values.Keys中的最后一个值
                    if (values.Keys.Last() == key)
                    {
                        sb_key.Append(key);
                        sb_value.Append("'" + values[key] + "'");
                        break;
                    }



                    //把values中的键值对分别放入sb_key和sb_value
                    sb_key.Append(key + ',');
                    sb_value.Append("'" + values[key] + "',");

                }
            }
            string sqlstr = $"insert into [dbo].[{deviceName}_device_data]({sb_key.ToString()})  values  ({sb_value.ToString()})";
            if (sb_key.Length <= 0)
            {
                return string.Empty;
            }

            return SetDatas(sqlstr);

        }

        //根据code值查找表中是否有code值的数据，如果有则更新该code行数据，如果没有则创建新的code行数据
        public string UpdateData(string devicesName, Dictionary<string, string> values)
        {
            StringBuilder sb_key = new StringBuilder();
            StringBuilder sb_value = new StringBuilder();
            string code = values["code"];
            string sqlstr = $"select * from {devicesName}_device_data where code = '{code}' and create_time > CONVERT(date, GETDATE()) and create_time > CONVERT(date, GETDATE())";
            DataTable dt = GetDatas(sqlstr);
            if (dt.Rows.Count > 0)
            {
                //更新
                foreach (string key in values.Keys)
                {
                    if (TableColmns[devicesName].Contains(key))
                    {
                        //判断key是否是values.Keys中的最后一个值
                        if (values.Keys.Last() == key)
                        {
                            sb_key.Append(key + '=');
                            sb_key.Append("'" + values[key] + "'");
                            break;
                        }
                        sb_key.Append(key + '=');
                        sb_key.Append("'" + values[key] + "',");
                    }
                }
                sqlstr = $"update {devicesName}_device_data set {sb_key.ToString()} where code = '{code}'";
                return SetDatas(sqlstr);
            }
            else
            {
                //插入
                return SetData(devicesName, values);
            }

        }

        //获取表的全部列名
        public List<string> GetColumns(string tableName)
        {
            List<string> columns = new List<string>();
            try
            {
                conn = new SqlConnection(dbConfig);
                conn.Open();
                SqlCommand cmd = new SqlCommand($"select * from [dbo].[{tableName}]", conn);
                SqlDataReader reader = cmd.ExecuteReader();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    columns.Add(reader.GetName(i));
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                this.Dispose();
            }
            return columns;
        }

        public DataTable GetDatas(string deviceName, string sql)
        {
            DataTable dt = new DataTable();

            try
            {

                // DataTable dt =  DataTableHelper.CreateTableFromType<TestResultModel>();


                var columns = GetColumns(deviceName);
                foreach (var item in columns)
                {
                    dt.Columns.Add(item);
                }

                using (conn = new SqlConnection(dbConfig))
                {

                    SqlCommand command = new SqlCommand(sql, conn);

                    conn.Open();

                    //使用SqlDataReader替代SqlDataAdapter

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        dt.Load(reader);
                    }

                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                // 记录异常信息到日志中
                logger.Error("数据库异常" + ex.Message);
                // 关闭数据库连接

                return dt;

            }
            finally
            {
                this.Dispose();
            }
            // TransColumnName2Chinese(ref dt);
            return dt;

        }

        public DataTable GetDatas(string sql)
        {
            DataTable dt = new DataTable();

            try
            {
      

                using (conn = new SqlConnection(dbConfig))
                {

                    SqlCommand command = new SqlCommand(sql, conn);

                    conn.Open();

                    //使用SqlDataReader替代SqlDataAdapter

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        dt.Load(reader);
                    }

                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                // 记录异常信息到日志中
                logger.Error("数据库异常" + ex.Message);
                // 关闭数据库连接

                return dt;

            }
            finally
            {
                this.Dispose();
            }
            // TransColumnName2Chinese(ref dt);
            return dt;

        }
        public DataTable GetDatasByReader(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                using (conn = new SqlConnection(dbConfig))
                {

                    SqlCommand command = new SqlCommand(sql, conn);

                    conn.Open();

                    //使用SqlDataReader替代SqlDataAdapter

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        dt.Load(reader);
                    }

                    conn.Close();



                }


            }
            catch (Exception ex)
            {
                // 记录异常信息到日志中
                logger.Debug(ex.Message);
                // 关闭数据库连接
                if (conn?.State != ConnectionState.Closed)
                {
                    conn?.Close();
                }
                return dt;


            }
            finally
            {
                this.Dispose();
            }

            return dt;

        }

        public bool AddDataTableToDB(DataTable source, string tableName)
        {
            if (!TableExists(new SqlConnection(dbConfig), tableName))
            {
                // If the table does not exist, create it using the DataTable's structure
                CreateDBTable(new SqlConnection(dbConfig), source, tableName);
            }


            SqlTransaction? tran = null;//声明一个事务对象
            try
            {

                using (SqlConnection conn = new SqlConnection(dbConfig))
                {
                    conn.Open();//打开链接
                    using (tran = conn.BeginTransaction())
                    {
                        // Check if the table exists in the database

                        // Continue with the SqlBulkCopy operation
                        using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
                        {
                            copy.DestinationTableName = $"[{tableName}]";  //指定服务器上目标表的名称
                            copy.WriteToServer(source);  //执行把DataTable中的数据写入DB  注意DataTable表结构要与数据库表结构一样
                            tran.Commit();              //提交事务
                            return true;               //返回True 执行成功！
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (null != tran)
                    tran.Rollback();
                logger.Error(ex);
                // MessageBox.Show($"{ex}","错误");
                return false;//返回False 执行失败！
            }
        }

        // 检查表在数据库中是否存在
        private bool TableExists(SqlConnection conn, string tableName)
        {
            try
            {
                conn.Open();//打开链接
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand($"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableName}'", conn, transaction))
                    {
                        try
                        {
                            //  conn.Open();
                            int count = (int)cmd.ExecuteScalar();
                            transaction.Commit();
                            return count > 0;
                        }
                        catch (Exception)
                        {
                            if (transaction.Connection != null && conn.State != ConnectionState.Closed)
                            {
                                transaction.Rollback();
                            }

                            throw;
                        }

                    }
                }
            }
            catch (Exception e)
            {
                // Console.WriteLine(e);
                logger.Error(e);
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }

        }

        // Create a table in the database using the structure of a DataTable
        private void CreateDBTable(SqlConnection conn, DataTable source, string tableName)
        {
            try
            {
                conn.Open();//打开链接

                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand($"CREATE TABLE [{tableName}] ({GetTableColumns(source)})", conn, transaction))
                    {

                        cmd.ExecuteNonQuery();
                        transaction.Commit();
                    }
                }
            }
            catch (Exception e)
            {
                // Console.WriteLine(e);

                logger.Error(e);
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }




        }

        // Get the columns of a DataTable in a format suitable for creating a SQL table
        private string GetTableColumns(DataTable table)
        {
            StringBuilder columns = new StringBuilder();
            foreach (DataColumn column in table.Columns)
            {
                columns.Append($"[{column.ColumnName}] {GetSqlType(column.DataType)}, ");
            }
            columns.Length -= 2; // Remove the trailing comma and space
            return columns.ToString();
        }

        // Map .NET data types to SQL data types
        private string GetSqlType(Type type)
        {
            if (type == typeof(int))
                return "INT";
            else if (type == typeof(string))
                return "NVARCHAR(30)"; // You may adjust the size based on your needs
            else if (type == typeof(DateTime))
            {
                return "DATETIME";
            }
            // Add more data type mappings as needed
            else
                throw new NotSupportedException($"Unsupported data type: {type}");
        }

    }
}
